package com.spacing.app;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.spacing.app.Tables.PracticeDate;
import com.spacing.app.Tables.SpacerData;
import com.spacing.app.Tables.WordsEncounterTable;
import com.spacing.app.Tables.WordsTable;

public class SchemaHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "adv_data.db";
	// TOGGLE THIS NUMBER FOR UPDATING TABLES AND DATABASE
	private static final int DATABASE_VERSION = 4;

	SchemaHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// CREATE WORDS TABLE
		db.execSQL("CREATE TABLE " + WordsTable.TABLE_NAME + " ("
				+ WordsTable.ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ WordsTable.CHAPTER_ID + " INTEGER," + WordsTable.CHAPTER_NAME
				+ " TEXT," + WordsTable.SECTION_ID + " INTEGER,"
				+ WordsTable.SECTION_NAME + " TEXT," + WordsTable.WORD1
				+ " TEXT," + WordsTable.WORD2 + " TEXT);");

		db.execSQL("CREATE TABLE " + WordsEncounterTable.TABLE_NAME + " ("
				+ WordsEncounterTable.ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ WordsEncounterTable.WORD + " TEXT);");

		db.execSQL("CREATE TABLE " + Tables.SpacerData.TABLE_NAME + " ("
				+ SpacerData.ID			+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ SpacerData.METHOD 	+ " TEXT,"
				+ SpacerData.WORD 		+ " TEXT,"
				+ SpacerData.CORRECT 	+ " BOOLEAN,"
				+ SpacerData.ABSTIME 	+ " LONG,"
				+ SpacerData.ALPHA	 	+ " DOUBLE,"
				+ SpacerData.TIME 		+ " LONG,"
				+ SpacerData.VALUE 		+ " DOUBLE,"
				+ SpacerData.DECAY 		+ " DOUBLE);");

		db.execSQL("CREATE TABLE " + Tables.PracticeDate.TABLE_NAME + " ("
				+ PracticeDate.ID		+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ PracticeDate.SECTION	+ " TEXT,"
				+ PracticeDate.DATE 	+ " TEXT);");
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w("LOG_TAG", "Upgrading database from version " + oldVersion
				+ " to " + newVersion + ",	which will destroy all old data");
		// KILL PREVIOUS TABLES IF UPGRADED
		db.execSQL("DROP TABLE IF EXISTS " + WordsTable.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + SpacerData.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + Tables.PracticeDate.TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + Tables.WordsEncounterTable.TABLE_NAME);
		onCreate(db);
	}
	
	// WRAPPER METHOD FOR ADDING A WORD
	public long addDatePracticed(String section, String date) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { PracticeDate.SECTION };
		String[] selectionArgs = new String[] { String.valueOf(section) };
		Cursor c = sd.query(PracticeDate.TABLE_NAME, cols,
				PracticeDate.SECTION + "= ?", selectionArgs, null, null,null);

		ContentValues cv = new ContentValues();
		cv.put(PracticeDate.SECTION, section);
		cv.put(PracticeDate.DATE, date);
	
		if (c.getCount() > 0){
			long result = sd.update(PracticeDate.TABLE_NAME, cv, PracticeDate.SECTION + "= ?", selectionArgs);
			return result;
		} else{
			long result = sd.insert(PracticeDate.TABLE_NAME, PracticeDate.SECTION, cv);
			return result;
		}
	}
	
	// GET WORD
	public String getDatepracticed(String section) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { PracticeDate.DATE };
		String[] selectionArgs = new String[] { section };
		Cursor c = sd.query(PracticeDate.TABLE_NAME, cols, PracticeDate.SECTION + "= ?", selectionArgs,	null, null, null);
		String name = "-";
		while (c.moveToNext()) {
			int colid = c.getColumnIndex(PracticeDate.DATE);
			name = c.getString(colid);
		}
		return name;
	}

	// WRAPPER METHOD FOR ADDING A WORD
	public long addWord(int chapter_id, String chapter_name, int section_id,
			String section_name, String word1, String word2) {
		// CREATE A CONTENTVALUE OBJECT
		ContentValues cv = new ContentValues();
		cv.put(WordsTable.CHAPTER_ID, chapter_id);
		cv.put(WordsTable.CHAPTER_NAME, chapter_name);
		cv.put(WordsTable.SECTION_ID, section_id);
		cv.put(WordsTable.SECTION_NAME, section_name);
		cv.put(WordsTable.WORD1, word1);
		cv.put(WordsTable.WORD2, word2);
		// RETRIEVE WRITEABLE DATABASE AND INSERT
		SQLiteDatabase sd = getWritableDatabase();
		long result = sd.insert(WordsTable.TABLE_NAME, WordsTable.WORD1, cv);
		return result;
	}

	// GET WORD
	public Cursor getWord(int wordId) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { WordsTable.ID, WordsTable.WORD1,
				WordsTable.WORD2 };
		String[] selectionArgs = new String[] { String.valueOf(wordId) };
		Cursor c = sd.query(WordsTable.TABLE_NAME, cols, WordsTable.ID + "= ?",
				selectionArgs, null, null, null);
		return c;
	}

	// GET WORD
	public Cursor getWords(int chapter_id, int section_id) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { WordsTable.ID, WordsTable.WORD1,
				WordsTable.WORD2 };
		String[] selectionArgs = new String[] { String.valueOf(chapter_id),
				String.valueOf(section_id) };
		Cursor c = sd.query(WordsTable.TABLE_NAME, cols, WordsTable.CHAPTER_ID
				+ "= ? AND " + WordsTable.SECTION_ID + "= ? ", selectionArgs,
				null, null, null);
		return c;
	}

	public String[] getChapterNames() {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { WordsTable.CHAPTER_NAME };
		String[] selectionArgs = new String[] {};
		Cursor c = sd.query(WordsTable.TABLE_NAME, cols, null, selectionArgs,
				WordsTable.CHAPTER_NAME, null, null);
		String[] names = new String[c.getCount()];
		while (c.moveToNext()) {
			int colid = c.getColumnIndex(WordsTable.CHAPTER_NAME);
			names[c.getPosition()] = c.getString(colid);
		}
		return names;
	}

	public String[] getSectionNames(int section) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { WordsTable.SECTION_NAME };
		String[] selectionArgs = new String[] { String.valueOf(section) };
		Cursor c = sd.query(WordsTable.TABLE_NAME, cols, WordsTable.CHAPTER_ID
				+ "= ?", selectionArgs, WordsTable.SECTION_NAME, null, null);
		String[] names = new String[c.getCount()];
		while (c.moveToNext()) {
			int colid = c.getColumnIndex(WordsTable.SECTION_NAME);
			names[c.getPosition()] = c.getString(colid);
		}
		return names;
	}

	public int getSize() {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { WordsTable.CHAPTER_ID };
		String[] selectionArgs = new String[] {};
		Cursor c = sd.query(WordsTable.TABLE_NAME, cols, null, selectionArgs,
				WordsTable.CHAPTER_ID, null, null);
		return c.getCount();

	}

	public int getChapterSize(int chapter_id) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { WordsTable.ID };
		String[] selectionArgs = new String[] { String.valueOf(chapter_id) };
		Cursor c = sd.query(WordsTable.TABLE_NAME, cols, WordsTable.CHAPTER_ID
				+ "= ?", selectionArgs, WordsTable.SECTION_ID, null, null);
		return c.getCount();
	}

	public int getSectionSize(int chapter_id, int section_id) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { WordsTable.ID };
		String[] selectionArgs = new String[] { String.valueOf(chapter_id),
				String.valueOf(section_id) };
		Cursor c = sd.query(WordsTable.TABLE_NAME, cols, WordsTable.CHAPTER_ID
				+ "= ? AND " + WordsTable.SECTION_ID + "= ?", selectionArgs,
				null, null, null);
		return c.getCount();
	}

	public String[] getWordlist2(int chapter_id, int section_id) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { WordsTable.WORD2 };
		String[] selectionArgs = new String[] { String.valueOf(chapter_id),
				String.valueOf(section_id) };
		Cursor c = sd.query(WordsTable.TABLE_NAME, cols, WordsTable.CHAPTER_ID
				+ "= ? AND " + WordsTable.SECTION_ID + "= ?", selectionArgs,
				null, null, null);
		String[] names = new String[c.getCount()];
		while (c.moveToNext()) {
			int colid = c.getColumnIndex(WordsTable.WORD2);
			names[c.getPosition()] = c.getString(colid);
		}
		return names;
	}

	public String[] getWordlist1(int chapter_id, int section_id) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { WordsTable.WORD1 };
		String[] selectionArgs = new String[] { String.valueOf(chapter_id),
				String.valueOf(section_id) };
		Cursor c = sd.query(WordsTable.TABLE_NAME, cols, WordsTable.CHAPTER_ID
				+ "= ? AND " + WordsTable.SECTION_ID + "= ?", selectionArgs,
				null, null, null);
		String[] names = new String[c.getCount()];
		while (c.moveToNext()) {
			int colid = c.getColumnIndex(WordsTable.WORD1);
			names[c.getPosition()] = c.getString(colid);
		}
		return names;
	}

	// WRAPPER METHOD FOR ADDING A WORD
	public long addEncounter(String word) {
		// CREATE A CONTENTVALUE OBJECT
		ContentValues cv = new ContentValues();
		cv.put(WordsEncounterTable.WORD, word);
		// RETRIEVE WRITEABLE DATABASE AND INSERT
		SQLiteDatabase sd = getWritableDatabase();
		long result = sd.insert(WordsEncounterTable.TABLE_NAME,
				WordsEncounterTable.WORD, cv);
		return result;
	}

	public boolean isEncounter(String word) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { WordsEncounterTable.WORD };
		String[] selectionArgs = new String[] { String.valueOf(word) };
		Cursor c = sd.query(WordsEncounterTable.TABLE_NAME, cols,
				WordsEncounterTable.WORD + "= ?", selectionArgs, null, null,
				null);
		return c.getCount() > 0;
	}

	public void beginTransaction() {
		// TODO Auto-generated method stub
		SQLiteDatabase sd = getWritableDatabase();
		sd.beginTransaction();
	}

	public void endTransaction() {
		// TODO Auto-generated method stub
		SQLiteDatabase sd = getWritableDatabase();
		sd.endTransaction();
	}

	public void setTransactionSuccessful() {
		// TODO Auto-generated method stub
		SQLiteDatabase sd = getWritableDatabase();
		sd.setTransactionSuccessful();
	}

	
	// WRAPPER METHOD FOR ADDING ENCOUNTER OF A WORD (WORD+TIME)
	public long addSpacerData(String word, String method, boolean correct, double abstime, double alpha, long time, double value, double decay) {
		// CREATE A CONTENTVALUE OBJECT
		ContentValues cv = new ContentValues();
		cv.put(SpacerData.WORD, word);
		cv.put(SpacerData.METHOD, method);
		cv.put(SpacerData.CORRECT, correct);
		cv.put(SpacerData.ABSTIME, abstime);
		cv.put(SpacerData.ALPHA, alpha);
		cv.put(SpacerData.TIME, time);
		cv.put(SpacerData.VALUE, value);
		cv.put(SpacerData.DECAY, decay);
		// RETRIEVE WRITEABLE DATABASE AND INSERT
		SQLiteDatabase sd = getWritableDatabase();
		long result = sd.insert(SpacerData.TABLE_NAME, SpacerData.WORD, cv);
		return result;
	}

	// WRAPPER METHOD FOR GETTING THE ENCOUNTERS FROM PREVIOUS SESSIONS OF A
	// WORD
	public Cursor getEncounters(String word, String method) {
		SQLiteDatabase sd = getWritableDatabase();
		String[] cols = new String[] { SpacerData.ABSTIME, SpacerData.ALPHA,
				SpacerData.TIME, SpacerData.VALUE, SpacerData.DECAY };
		String[] selectionArgs = new String[] { word, method };
		Cursor c = sd.query(SpacerData.TABLE_NAME, cols, SpacerData.WORD
				+ "= ? AND "+ SpacerData.METHOD	+ "= ?", selectionArgs, null, null, null);

		return c;
	}

}